package com.clover.coder.core;

import org.apache.log4j.Logger;

import java.io.*;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Properties;

/**
 * 数据库操作管理类
 * @author masl
 */
public class DBHelper {
	private static Logger logger = Logger.getLogger(DBHelper.class);
	private static DBHelper instance;

	private void DBHelper() {
	}

	public static DBHelper getInstance() {
		if (instance == null) {
			instance = new DBHelper();
			instance.getConnection();
		}
		return instance;
	}

	public static void main(String args[]) throws Exception {

		// DBHelper dbHelper = new DBHelper();
		// sqlite 数据库连接测试
		/*
		 * sqlite 数据库连接测试 dbHelper.set_DRIVER("org.sqlite.JDBC");
		 * dbHelper.set_URL("jdbc:sqlite:d:/Task.db");
		 * dbHelper.set_DB_TYPE(DataBaseType.sqlite); ArrayList<HashMap<Object,
		 * Object>> al = dbHelper .executeSql("SELECT * FROM AutoTask");
		 */

		// sqlserver 数据库连接测试
		/*
		 * dbHelper.set_DRIVER("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		 * dbHelper.set_URL("jdbc:sqlserver://192.168.100.15;database=OA;");
		 * dbHelper.set_USER_NA("sa"); dbHelper.set_PASSWORD("123456");
		 * dbHelper.set_DB_TYPE(DataBaseType.sqlserver);
		 * ArrayList<HashMap<Object, Object>> al =
		 * dbHelper.executeSql("SELECT TOP 5 * FROM A_Operations ");
		 */

		// mysql 数据库连接测试

		DBHelper dbHelper = new DBHelper();
		dbHelper.setDRIVER("com.mysql.jdbc.Driver");
		dbHelper.setURL("jdbc:mysql://localhost:3306/ssm-web?useUnicode=true&characterEncoding=utf-8");
		dbHelper.setUSER_NA("root");
		dbHelper.setPASSWORD("123456");
		dbHelper.setDB_TYPE(DataBaseType.mysql);

		String pathname = "D:\\11.txt";
		try {
			//FileReader reader = new FileReader(pathname);
			InputStreamReader
					reader = new InputStreamReader(
					new FileInputStream(pathname), "gb2312");//考虑到编码格式

			BufferedReader br = new BufferedReader(reader); // 建立一个对象，它把文件内容转成计算机能读懂的语言
			String line;
			int num = 0;
			int totalNum = 0;
			StringBuilder sql = new StringBuilder();
			//网友推荐更加简洁的写法
			while ((line = br.readLine()) != null) {
				totalNum++;
				num++;
				System.out.println("当前读取第 " + totalNum + " 行");
				sql.append(line);
				if (num > 200) {
					String tmpStr = sql.toString();
					if (tmpStr.endsWith(","))
						tmpStr = tmpStr.substring(0, tmpStr.length() - 1) + ";";

					dbHelper.executeMutiSql("INSERT INTO `sys_region`(`region_code`,`region_name`,`region_level`,`parent_region_code`) VALUES " + tmpStr);
					num = 0;
					sql = new StringBuilder();
				}
			}

		} catch (IOException e) {
			e.printStackTrace();
		}

		/*ArrayList<HashMap<Object, Object>> al = dbHelper.executeSql("SELECT  * FROM sys_item ");
		Tools.logger.info(al.size());

		List<String> list = dbHelper.getTables();
		for (String tmp : list) {
			Tools.logger.info(tmp);
		}
		String pKey = "";
		dbHelper.getColumns("a_ad");*/
		//
	}

	public static void readFile() {
		String pathname = "D:\\sys_region.sql";
		try {
			FileReader reader = new FileReader(pathname);
			BufferedReader br = new BufferedReader(reader); // 建立一个对象，它把文件内容转成计算机能读懂的语言
			String line;
			int num=0;
			StringBuilder sql = new StringBuilder();
			//网友推荐更加简洁的写法
			while ((line = br.readLine()) != null) {
				// 一次读入一行数据
				sql.append("");
			}

		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	// 数据库连接对象
	private Connection con;
	// SQL语句对象
	private Statement stmt;
	// 带参数的Sql语句对象
	private PreparedStatement pstmt;
	// 记录集对象
	private ResultSet rs;
	// 数据连接管理（连接池对象）
	//private DBConnectionManager dcm = null;

	/** ***********************手动设置的连接参数********************************* */
	private static String _DRIVER = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
	private static String _URL = "jdbc:sqlserver://192.168.100.15;database=OA;";
	private static String _USER_NA = "";
	private static String _PASSWORD = "";
	private static DataBaseType _DB_TYPE = DataBaseType.sqlserver;

	/** ********************************************************************** */

	// 默认构造
	public DBHelper() {
	}

	/**
	 * 得到一个默认的数据库连接[从 com.hospital.dao.tools.db.properties文件初始化]
	 * 
	 * @throws Exception
	 */
	/*
	 * private void getConnection() {
	 * logger.info("###############open:::::从默认的配置文件得到一个数据库连接"); //
	 * 获取一个连接池管理类的实例 dcm = DBConnectionManager.getInstance(); // 得到一个数据库连接 con =
	 * dcm.getConnection(_DB_TYPE.name()); try { con.setAutoCommit(false); }
	 * catch (SQLException e) { e.printStackTrace(); } }
	 */

	/**
	 * 得到一个默认的数据库连接[从 resources/db.properties 文件初始化]
	 * 
	 * @throws Exception
	 */
	private void getConnection() {
		InputStream is = getClass().getResourceAsStream("/db.properties");
		Properties dbProps = new Properties();
		try {
			dbProps.load(is);
		} catch (Exception e) {
			System.err.println("Can't read the properties file. " + "Make sure db.properties is in the CLASSPATH");
			return;
		}
		_DRIVER = dbProps.getProperty("drivers");
		_URL = dbProps.getProperty("url");
		if (_URL == null) {
			// log("No URL specified for ");
			return;
		}
		_USER_NA = dbProps.getProperty("user");
		_PASSWORD = dbProps.getProperty("password");
		String dbType = dbProps.getProperty("dbtype");
		switch (dbType) {
		case "mysql":
			_DB_TYPE = DataBaseType.mysql;
			break;
		case "sqlite":
			_DB_TYPE = DataBaseType.sqlite;
			break;
		case "orcal":
			_DB_TYPE = DataBaseType.orcal;
			break;
		case "sqlserver":
			_DB_TYPE = DataBaseType.sqlserver;
			break;
		default:
			_DB_TYPE = DataBaseType.mysql;
			break;
		}
	}

	/**
	 * 从指定参数得到一个连接对象
	 * 
	 * @param driver
	 * @param url
	 * @param user_na
	 * @param password
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 * @throws Exception
	 */
	public void getConnection(String driver, String url, String user_na, String password) throws Exception {
		try {
			logger.info("###############open:::::从指定配置中得到一个数据库连接");
			Class.forName(driver);
			con = DriverManager.getConnection(url, user_na, password);
		} catch (ClassNotFoundException ex) {
			logger.info("###############Error[com.lin.dao.DBHelper^^^Method:getConnection^^^Line:81]找不到类驱动类: " + driver);
			throw ex;
		} catch (SQLException ex) {
			logger.info("###############Error[com.lin.dao.DBHelper^^^Method:getConnection^^^Line:81]加载类: " + driver + " 时出现 SQLException 异常");
			throw ex;
		}
	}

	/**
	 * @throws SQLException 
	 * @throws ClassNotFoundException 
	 * @Title: getTables 
	 * @Description: 获取数据库中所有表名
	 * @author: masl 2016年8月6日上午9:57:59
	 * @param @return 
	 * @return List<String>
	 * @throws
	 */
	public List<String> getTables() throws ClassNotFoundException, SQLException {
		List<String> list = new ArrayList<String>();
		try {
			getConnection(_DRIVER, _URL, _USER_NA, _PASSWORD);
			DatabaseMetaData dbmd = con.getMetaData();
			ResultSet resultSet = dbmd.getTables(null, "%", "%", new String[] { "TABLE" });
			while (resultSet.next()) {
				String tableObj = resultSet.getString("TABLE_NAME");
				list.add(tableObj);
			}
		} catch (SQLException ex) {
			logger.info("###############Error DBHelper:getTables失败!");
			throw ex;
		} catch (ClassNotFoundException ex) {
			logger.info("###############Error DBHelper:getTables失败! 参数设置类型错误!");
			throw ex;
		} catch (Exception e) {
			logger.info("###############Error DBHelper::getTables失败! 未知错误!" + e.getStackTrace());
		} finally {
			closeDB();
		}
		return list;
	}

	/**
	 * 取表主键
	 * @Auther: masl 2017年5月28日上午11:27:48
	 * @param tableName
	 */
	public String getKeyN(String tableName) {
		String pKey = "id";//主键
		try {
			getConnection(_DRIVER, _URL, _USER_NA, _PASSWORD);
			DatabaseMetaData dbmd = con.getMetaData();
			ResultSet rs = dbmd.getPrimaryKeys(null, null, tableName);
			while (rs.next()) {
				pKey = rs.getString(4);
			}
		} catch (Exception e) {
			logger.info("###############Error DBHelper::getColumns失败! 未知错误!" + e.getStackTrace());
		} finally {
			closeDB();
		}
		return pKey;
	}
	/**
	 * @Title: getColumns 
	 * @Description: TODO
	 * @author: masl 2016年8月6日上午11:01:33
	 * @param @param tableName
	 * @param @return
	 * @param @throws ClassNotFoundException
	 * @param @throws SQLException 
	 * @return List<String[]>
	 * @throws
	 */
	public List<String[]> getColumns(String tableName) throws ClassNotFoundException, SQLException {
		List<String[]> fieldList = new ArrayList<String[]>();
		try {
			getConnection(_DRIVER, _URL, _USER_NA, _PASSWORD);
			DatabaseMetaData dbmd = con.getMetaData();
			ResultSet rs = dbmd.getPrimaryKeys(null, null, tableName);
			rs = dbmd.getColumns(null, "%", tableName, "%");
			while (rs.next()) {
				String name = rs.getString("COLUMN_NAME");
				String[] arr = { name, rs.getString("TYPE_NAME"), rs.getString("REMARKS") };
				fieldList.add(arr);
			}
		} catch (SQLException ex) {
			logger.info("###############Error DBHelper:getColumns失败!");
			throw ex;
		} catch (ClassNotFoundException ex) {
			logger.info("###############Error DBHelper:getColumns失败! 参数设置类型错误!");
			throw ex;
		} catch (Exception e) {
			logger.info("###############Error DBHelper::getColumns失败! 未知错误!" + e.getStackTrace());
		} finally {
			closeDB();
		}
		return fieldList;
	}

	/**
	 * 执行SQL语句操作(更新数据 无参数)
	 * 
	 * @param strSql
	 *            SQL语句
	 * @throws Exception
	 */
	public boolean executeUpdate(String strSql) throws Exception {
		//getConnection();
		getConnection(_DRIVER, _URL, _USER_NA, _PASSWORD);
		boolean flag = false;
		stmt = con.createStatement();
		logger.info("###############::执行SQL语句操作(更新数据 无参数):" + strSql);
		try {
			if (0 < stmt.executeUpdate(strSql)) {
				flag = true;
				//con.commit();
			}
		} catch (SQLException ex) {
			logger.info("###############Error DBHelper Line126::执行SQL语句操作(更新数据 无参数):" + strSql + "失败!");
			flag = false;
			con.rollback();
			throw ex;
		} finally {
			closeDB();
		}
		return flag;

	}

	/**
	 * 执行SQL语句操作(更新数据 有参数)
	 * 
	 * @param strSql
	 *            sql指令
	 * @param prams
	 *            参数列表
	 * @return
	 * @throws Exception
	 */
	public boolean executeUpdate(String strSql, HashMap<Integer, Object> prams) throws Exception {
		//getConnection();
		getConnection(_DRIVER, _URL, _USER_NA, _PASSWORD);
		boolean flag = false;
		try {
			pstmt = con.prepareStatement(strSql);
			setParamet(pstmt, prams);
			logger.info("###############::执行SQL语句操作(更新数据 有参数):" + strSql);

			if (0 < pstmt.executeUpdate()) {
				flag = true;
				//con.commit();
			}
		} catch (SQLException ex) {
			logger.info("###############Error DBHelper:执行SQL语句操作(更新数据 无参数):" + strSql + "失败!");
			flag = false;
			con.rollback();
			throw ex;
		} catch (ClassNotFoundException ex) {
			logger.info("###############Error DBHelper Line152::执行SQL语句操作(更新数据 无参数):" + strSql + "失败! 参数设置类型错误!");
			con.rollback();
			throw ex;
		} finally {
			closeDB();
		}
		return flag;
	}

	/**
	 * 执行SQL语句操作(查询数据 无参数)
	 * 
	 * @param strSql
	 *            SQL语句
	 * @return 数组对象列表
	 * @throws Exception
	 */
	public ArrayList<HashMap<Object, Object>> executeSql(String strSql) throws Exception {
		//getConnection();
		getConnection(_DRIVER, _URL, _USER_NA, _PASSWORD);
		try {
			stmt = con.createStatement();
			logger.info("###############::执行SQL语句操作(查询数据):" + strSql);
			rs = stmt.executeQuery(strSql);
			// con.commit();
			if (null != rs) {
				return convertResultSetToArrayList(rs);
			}

		} catch (Exception e) {
			logger.info("###############Error DBHelper Line152::执行SQL语句操作(更新数据 无参数):" + strSql + "失败!" + e.getMessage());
		} finally {
			closeDB();
		}
		return null;
	}

	public HashMap<Object, Object> findOne(String strSql) throws Exception {
		//getConnection();
		getConnection(_DRIVER, _URL, _USER_NA, _PASSWORD);
		try {
			stmt = con.createStatement();
			logger.info("###############::执行SQL语句操作(查询数据):" + strSql);
			rs = stmt.executeQuery(strSql);
			if (null != rs) {
				ArrayList<HashMap<Object, Object>> list = convertResultSetToArrayList(rs);
				if (list != null && list.size() > 0)
					return list.get(0);
			}
		} catch (Exception e) {
			logger.info("###############Error DBHelper Line152::执行SQL语句操作 findOne:" + strSql + "失败!" + e.getMessage());
		} finally {
			closeDB();
		}
		return null;
	}

	public int getCount(String strSql) throws Exception {
		//getConnection();
		getConnection(_DRIVER, _URL, _USER_NA, _PASSWORD);
		try {

			stmt = con.createStatement();
			logger.info("###############::执行SQL语句操作(查询数据):" + strSql);
			rs = stmt.executeQuery(strSql);
			if (null != rs) {
				ResultSetMetaData rsmd = rs.getMetaData();
				while (rs.next()) {
					return rs.getInt(1);
				}
			}
		} catch (Exception e) {
			logger.info("###############Error DBHelper Line152::执行SQL语句操作 getCount:" + strSql + "失败!" + e.getMessage());
		} finally {
			closeDB();
		}
		return 0;
	}

	public String getFirstField(String strSql) throws Exception {
		//getConnection();
		getConnection(_DRIVER, _URL, _USER_NA, _PASSWORD);
		try {
			stmt = con.createStatement();
			logger.info("###############::执行SQL语句操作(查询数据):" + strSql);
			rs = stmt.executeQuery(strSql);
			if (null != rs) {
				ResultSetMetaData rsmd = rs.getMetaData();
				while (rs.next()) {
					return rs.getString(1);
				}
			}
		} catch (Exception e) {
			logger.info("###############Error DBHelper Line152::执行SQL语句操作 getFirstField:" + strSql + "失败!" + e.getMessage());
		} finally {
			closeDB();
		}
		return "";
	}

	public ArrayList<HashMap<Object, Object>> executeSql(String strSql, HashMap<Integer, Object> prams) throws Exception {
		//getConnection();
		getConnection(_DRIVER, _URL, _USER_NA, _PASSWORD);
		try {
			pstmt = con.prepareStatement(strSql);
			setParamet(pstmt, prams);
			logger.info("###############::执行SQL语句操作(查询数据):" + strSql);
			rs = pstmt.executeQuery();
			con.commit();
			if (null != rs) {
				return convertResultSetToArrayList(rs);
			}
		} catch (Exception e) {
			logger.info("###############Error DBHelper Line152::执行SQL语句操作 executeSql:" + strSql + "失败!" + e.getMessage());
		} finally {
			closeDB();
		}
		return null;
	}

	/**
	 * 执行多条 sql
	 * @param strSql
	 * @throws Exception
	 */
	public void executeMutiSql(String strSql) throws Exception {
		//getConnection();
		getConnection(_DRIVER, _URL, _USER_NA, _PASSWORD);
		try {
			Statement s = con.createStatement();
			s.addBatch(strSql);
			s.executeBatch();

			logger.info("###############::执行SQL语句操作(查询数据):" + strSql);
		} catch (Exception e) {
			logger.info("###############Error DBHelper Line152::执行SQL语句操作 executeSql:" + strSql + "失败!" + e.getMessage());
		} finally {
			closeDB();
		}
	}

	/**
	 * 关闭数据对象
	 */
	public void closeDB() {
		logger.info("###############close:::::关闭连接对象，语句对象，记录集对象");
		if (null != rs) {
			try {
				rs.close();
			} catch (SQLException ex) {
				rs = null;
			}
		}
		if (null != stmt) {
			try {
				stmt.close();
			} catch (SQLException ex) {
				stmt = null;
			}
		}
		if (null != pstmt) {
			try {
				pstmt.close();
			} catch (SQLException ex) {
				pstmt = null;
			}
		}
		if (con != null) {
			// dcm.freeConnection("sqlserver", con);
			try {
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * 设置Sql 指令参数
	 */
	private PreparedStatement setParamet(PreparedStatement p_stmt, HashMap<Integer, Object> pramets) throws ClassNotFoundException, SQLException {
		// 如果参数为空
		if (null != pramets) {
			// 如果参数个数为0
			if (0 <= pramets.size()) {
				for (int i = 1; i <= pramets.size(); i++) {
					try {
						// 字符类型 String
						if (pramets.get(i).getClass() == Class.forName("java.lang.String")) {
							p_stmt.setString(i, pramets.get(i).toString());
						}
						// 日期类型 Date
						if (pramets.get(i).getClass() == Class.forName("java.sql.Date")) {
							p_stmt.setDate(i, java.sql.Date.valueOf(pramets.get(i).toString()));
						}
						// 布尔类型 Boolean
						if (pramets.get(i).getClass() == Class.forName("java.lang.Boolean")) {
							p_stmt.setBoolean(i, (Boolean) (pramets.get(i)));
						}
						// 整型 int
						if (pramets.get(i).getClass() == Class.forName("java.lang.Integer")) {
							p_stmt.setInt(i, (Integer) pramets.get(i));
						}
						// 浮点 float
						if (pramets.get(i).getClass() == Class.forName("java.lang.Float")) {
							p_stmt.setFloat(i, (Float) pramets.get(i));
						}
						// 双精度型 double
						if (pramets.get(i).getClass() == Class.forName("java.lang.Double")) {
							p_stmt.setDouble(i, (Double) pramets.get(i));
						}

					} catch (ClassNotFoundException ex) {
						throw ex;
					} catch (SQLException ex) {
						throw ex;
					}
				}
			}
		}
		return p_stmt;
	}

	/**
	 * 转换记录集对象为数组列表对象
	 * 
	 * @param rs
	 *            纪录集合对象
	 * @return 数组列表对象
	 * @throws Exception
	 */
	private ArrayList<HashMap<Object, Object>> convertResultSetToArrayList(ResultSet rs) throws Exception {
		logger.info("###############::转换记录集对象为数组列表对象");
		// 创建数组列表集合对象
		ArrayList<HashMap<Object, Object>> tempList = new ArrayList<HashMap<Object, Object>>();
		try {
			ResultSetMetaData rsmd = rs.getMetaData();
			HashMap<Object, Object> tempHash = null;
			// 填充数组列表集合
			while (rs.next()) {
				// 创建键值对集合对象
				tempHash = new HashMap<Object, Object>();
				for (int i = 0; i < rsmd.getColumnCount(); i++) {
					// 遍历每列数据，以键值形式存在对象tempHash中
					tempHash.put(rsmd.getColumnName(i + 1).toUpperCase(), rs.getString(rsmd.getColumnName(i + 1)));
				}
				// 第一个键值对，存储在tempList列表集合对象中
				tempList.add(tempHash);
			}
		} catch (Exception e) {
			logger.info(e.getMessage());
		} finally {
			closeDB();
		}
		return tempList;// 返回填充完毕的数组列表集合对象
	}

	/**
	 * 得到数据类型的整型值
	 * 
	 * @param typeName
	 *            类型名称
	 * @return 数据类型的整型值
	 */
	private int getDataType(String typeName) {
		if (typeName.equals("varchar"))
			return Types.VARCHAR;
		if (typeName.equals("int"))
			return Types.INTEGER;
		if (typeName.equals("bit"))
			return Types.BIT;
		if (typeName.equals("float"))
			return Types.FLOAT;
		return 0;
	}

	// 设置驱动路径
	@SuppressWarnings("static-access")
	public void setDRIVER(String _DRIVER) {
		this._DRIVER = _DRIVER;
	}

	// 设置数据库密码
	@SuppressWarnings("static-access")
	public void setPASSWORD(String _PASSWORD) {
		this._PASSWORD = _PASSWORD;
	}

	// 设置数据库连接字符串
	@SuppressWarnings("static-access")
	public void setURL(String _URL) {
		this._URL = _URL;
	}

	// 设置数据库用户名
	@SuppressWarnings("static-access")
	public void setUSER_NA(String _USER_NA) {
		this._USER_NA = _USER_NA;
	}

	// 设置数据库类型
	@SuppressWarnings("static-access")
	public void setDB_TYPE(DataBaseType _DB_TYPE) {
		this._DB_TYPE = _DB_TYPE;
	}
}